﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using DbManager;
using DbManager.Connection;
using DbManager.DataManager;
using DbManager.QueryBuilder.DbEntity;
using DbManager.QueryBuilder.DbEntity.Bron;
using DbManager.QueryBuilder.DbEntity.Client;
using DbManager.QueryBuilder.DbEntity.Group;
using DbManager.QueryBuilder.DbEntity.IND.BazaOtdiha;
using DbManager.QueryBuilder.DbEntity.IND.Gorod;
using DbManager.QueryBuilder.DbEntity.IND.Napravlenie;
using DbManager.QueryBuilder.DbEntity.IND.Uslovia;
using DbManager.QueryBuilder.DbEntity.Komnata;
using DbManager.QueryBuilder.DbEntity.Pol;
using DbManager.QueryBuilder.DbEntity.Price;
using DbManager.QueryBuilder.DbEntity.Transport;
using DbManager.QueryBuilder.DbEntity.Viezd;
using Microsoft.Reporting.WinForms;
using TAProject.UI.Options;
using TAProject.UI.Order;
using TAProject.UI.Service.Price;
using TAProject.UI.Service.Room;
using TAProject.UI.Service.Terms;
using TAProject.UI.Service.Tour;
using TAProject.UI.Service.Transport;

namespace TAProject.UI
{
    public delegate void MainFormEvent();

    public partial class MainForm : Form
    {
        private readonly AEntityBazaOtdiha _aEntityBazaOtdiha;
        private readonly AEntityBron _aEntityBron;
        private readonly AEntityClient _aEntityClient;
        private readonly AEntityGorod _aEntityGorod;
        private readonly AEntityGroup _aEntityGroup;
        private readonly AEntityKomnata _aEntityKomnata;
        private readonly AEntityNapravlenie _aEntityNapravlenie;
        private readonly AEntityPrice _aEntityPrice;
        private readonly AEntityTransport _aEntityTransport;
        private readonly AEntityUslovia _aEntityUslovia;
        private readonly AEntityViezd _aEntityViezd;
        private readonly IConnectionManager _connectionManager;
        private readonly string _connectionString;
        private readonly IDataManager _dataManager;

        private int _currentRowIndex;
        private DataTable _dataTableBron;

        public event MainFormEvent UpdateDataTableBronEvent;

        public void InvokeDataGridBronUpdate()
        {
            if(UpdateDataTableBronEvent != null)
            {
                UpdateDataTableBronEvent();
            }
        }

        public MainForm()
        {
            InitializeComponent();

            //create connection string and give it to ConnectionManager
            _connectionString = ConfigurationManager.
                ConnectionStrings["DbManager.Properties.Settings.dbConnectionString"].ConnectionString;
            _connectionManager = new ConnectionManagerOleDb(_connectionString);
            _dataManager = new DataManagerOleDb();

            _aEntityClient = new EntityClient();
            _aEntityGroup = new EntityGroup();
            _aEntityPrice = new EntityPrice();
            _aEntityNapravlenie = new EntityNapravlenie();
            _aEntityGorod = new EntityGorodByNapravlenie();
            _aEntityBazaOtdiha = new EntityBazaOtdihaByGorod();
            _aEntityBron = new EntityBron();
            _aEntityViezd = new EntityViezd();
            _aEntityUslovia = new EntityUslovia();
            _aEntityKomnata = new EntityKomnata();
            _aEntityTransport = new EntityTransport();

            //выгружаем из БД данные в _mainDataTable
            UpdateDataTableBronEvent += UpdateDataTableBron;
            UpdateDataTableBron();
        }

        #region Main form events

        private void MainForm_Shown(object sender, EventArgs e)
        {
            dataGridViewBron.SelectionChanged += DataGridViewBronSelectionChanged;
        }

        #endregion

        #region MenuEvents

        private void RunForm(Form form)
        {
            try
            {
                form.ShowDialog(this);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
                Enabled = true;
            }
        }

        private void ToolStripMenuExitItemClick(object sender, EventArgs e)
        {
            Close();
        }

        private void ToolStripMenuCheckConnectionItemClick(object sender, EventArgs e)
        {
            //проверяем соедине5ние с БД
            bool connectionValid = _connectionManager.CheckConnection();
            toolStripStatusLabel.Text = !connectionValid
                                            ? "Подключение к БД не установлено. Проверьте настройки."
                                            : "Подключение к БД успешно установлено.";
        }

        private void ToolStripMenuItemManageTourClick(object sender, EventArgs e)
        {
            RunForm(new FormManageTour(this, _dataManager, _connectionManager));
        }

        private void ToolStripMenuItemManageTransportClick(object sender, EventArgs e)
        {
            RunForm(new FormManageTransport(this, _dataManager, _connectionManager));
        }

        private void ToolStripMenuItemUpdateDataGrid_Click(object sender, EventArgs e)
        {
            UpdateDataTableBron();
        }

        private void ToolStripMenuItemTarifsClick(object sender, EventArgs e)
        {
            var form = new FormOptions(this, _connectionManager, _dataManager);
            RunForm(form);
        }

        private void ToolStripMenuItemUsloviaClick(object sender, EventArgs e)
        {
            RunForm(new FormManageUslovia(this, _dataManager, _connectionManager));
        }

        private void ToolStripMenuItemRoomsClick(object sender, EventArgs e)
        {
            RunForm(new FormManageKomnata(this, _dataManager, _connectionManager));
        }

        private void ToolStripMenuItemPriceTransportClick(object sender, EventArgs e)
        {
            RunForm(new FormManagePot(this, _dataManager, _connectionManager));
        }

        private void ToolStripMenuItemPriceProjivanieClick(object sender, EventArgs e)
        {
            RunForm(new FormManagePol(this, _dataManager, _connectionManager));
        }

        #endregion

        #region +++ TAB Bron

        private void UpdateDataTableBron()
        {
            if (_connectionManager.CheckConnection())
            {
                //var str = _aEntityBron.GetSelectQuery();
                _dataTableBron = _dataManager.GetDataTable(_aEntityBron.GetSelectQuery(),
                                                           _connectionManager.GetConnection());
                if (_dataTableBron != null)
                {
                    FillDataGridBron();
                    _currentRowIndex = 0;
                }
                else
                {
                    MessageBox.Show("Ошибка получения данных из БД.");
                }
            }
            else
            {
                MessageBox.Show("Ошибка подключения к БД.");
                Close();
            }
        }

        private void FillDataGridBron()
        {
            dataGridViewBron.DataSource = _dataTableBron;
            //скрываем все ненужные столбцы
            for (int i = 0; i < dataGridViewBron.Columns.Count; i++)
            {
                if (dataGridViewBron.Columns[i].Name.StartsWith("Hide"))
                {
                    dataGridViewBron.Columns[i].Visible = false;
                }
            }
            if (dataGridViewBron.Rows.Count > 0)
            {
                UpdateDetailsBlock();
            }
        }

        private void UpdateDetailsBlock()
        {
            if (_currentRowIndex < 0) return;

            #region Viezd

            labelDataSbora.Text =
                ((DateTime) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdDataSbora"].Value).
                    ToShortDateString();
            labelVremiaSbora.Text =
                ((DateTime) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdVremiaSbora"].Value).
                    ToShortTimeString();
            labelMestoSbora.Text =
                dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdMestoSbora"].Value.ToString();

            #endregion

            #region Client

            labelClientName.Text = dataGridViewBron.Rows[_currentRowIndex].Cells["Фамилия"].Value + " " +
                                   dataGridViewBron.Rows[_currentRowIndex].Cells["Имя"].Value + " " +
                                   dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientFName"].Value;
            labelClientAdress.Text = dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientAdress"].Value.ToString();

            if (dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel1"].Value.ToString().ToSimpleTelephone() != string.Empty)
            {
                if (dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel2"].Value.ToString().ToSimpleTelephone() != string.Empty)
                {
                    labelClientTelephone.Text = String.Format("{0}, {1}", dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel1"].Value,
                        dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel2"].Value);
                }
                else
                {
                    labelClientTelephone.Text = String.Format("{0}", dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel1"].Value);
                }
            }
            else
            {
                if (dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel2"].Value.ToString().ToSimpleTelephone() != string.Empty)
                {
                    labelClientTelephone.Text = String.Format("{0}", dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientTel2"].Value);
                }
                else
                {
                    labelClientTelephone.Text = "Телефоны не указаны";
                }
            }

            #endregion

            #region Group

            _aEntityGroup.IDGroup = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDGroup"].Value;
            DataTable dataTableGroup = _dataManager.GetDataTable(_aEntityGroup.GetSelectQuery(),
                                                                 _connectionManager.GetConnection());
            if (dataTableGroup != null)
            {
                labelGroupVsego.Text = (dataTableGroup.AsEnumerable().
                                            Count() + 1).ToString();
                labelGroupAdult.Text = (dataTableGroup.AsEnumerable().
                                            Where(row => row.Field<bool>("Ребенок") == false).
                                            Count() + 1).ToString();
                labelGroupChildren.Text = dataTableGroup.AsEnumerable().
                    Where(row => row.Field<bool>("Ребенок")).
                    Count().ToString();
            }
            else
            {
                MessageBox.Show("Ошибка получения данных о группе.");
                return;
            }

            #endregion

            dataGridViewBron.Rows[_currentRowIndex].Selected = true;
        }

        private void ButtonBronAddClick(object sender, EventArgs e)
        {
            var form = new FormTourBron(this, _connectionManager, _dataManager);
            RunForm(form);
            _currentRowIndex = 0;
        }

        private void ButtonBronEditClick(object sender, EventArgs e)
        {
            if (_currentRowIndex < 0) return;
            ShowFormBron();
            _currentRowIndex = 0;
        }

        private void ShowFormBron()
        {
            _aEntityClient.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDClient"].Value;
            _aEntityClient.Surname = dataGridViewBron.Rows[_currentRowIndex].Cells["Фамилия"].Value.ToString();
            _aEntityClient.Name = dataGridViewBron.Rows[_currentRowIndex].Cells["Имя"].Value.ToString();
            _aEntityClient.FatherName =
                dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientFName"].Value.ToString();
            _aEntityClient.Adress = dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ClientAdress"].Value.ToString();

            _aEntityNapravlenie.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDNapravlenie"].Value;
            _aEntityNapravlenie.Name = dataGridViewBron.Rows[_currentRowIndex].Cells["Направление"].Value.ToString();

            _aEntityGorod.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDGorod"].Value;
            _aEntityGorod.Name = dataGridViewBron.Rows[_currentRowIndex].Cells["Город"].Value.ToString();

            _aEntityBazaOtdiha.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDBazaOtdiha"].Value;
            _aEntityBazaOtdiha.Name = dataGridViewBron.Rows[_currentRowIndex].Cells["База отдыха"].Value.ToString();

            _aEntityKomnata.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDKomnata"].Value;
            _aEntityKomnata.Klass = dataGridViewBron.Rows[_currentRowIndex].Cells["Класс"].Value.ToString();
            _aEntityKomnata.Mesta = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Мест"].Value;

            _aEntityUslovia.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDUslovia"].Value;
            _aEntityUslovia.Name = dataGridViewBron.Rows[_currentRowIndex].Cells["Условия"].Value.ToString();

            _aEntityTransport.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDTransport"].Value;
            _aEntityTransport.Car = dataGridViewBron.Rows[_currentRowIndex].Cells["Транспорт"].Value.ToString();

            _aEntityPrice.DiscountProjivanie =
                (decimal) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_DiscountProjivanie"].Value;
            _aEntityPrice.Transfer = (decimal) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_Transfer"].Value;
            _aEntityPrice.Bron = (decimal) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_Bron"].Value;
            _aEntityPrice.OplachenoProezd =
                (decimal) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_OplachenoProezd"].Value;
            _aEntityPrice.OplachenoProjivanie =
                (decimal) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_OplachenoProjivanie"].Value;

            _aEntityBron.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Номер"].Value;
            _aEntityBron.IDClient = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDClient"].Value;
            _aEntityBron.IDGroup = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDGroup"].Value;
            _aEntityBron.IDPol = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDPol"].Value;
            _aEntityBron.IDPot = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDPot"].Value;
            _aEntityBron.IDViezd = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDViezd"].Value;
            _aEntityBron.IDPrice = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDPrice"].Value;
            _aEntityBron.DateStartOfRest = (DateTime) dataGridViewBron.Rows[_currentRowIndex].Cells["Начало отд"].Value;
            _aEntityBron.DateEndOfRest = (DateTime) dataGridViewBron.Rows[_currentRowIndex].Cells["Конец отд"].Value;
            _aEntityBron.LongOfRest = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Срок"].Value;
            _aEntityBron.Discount = (bool) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_Discount"].Value;
            _aEntityBron.Insurance = (bool) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_Insurance"].Value;

            _aEntityViezd.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDViezd"].Value;
            _aEntityViezd.DataSbora =
                (DateTime) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdDataSbora"].Value;
            _aEntityViezd.VremiaSbora =
                (DateTime) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdVremiaSbora"].Value;
            _aEntityViezd.AdressClienta = _aEntityClient.Adress;
            _aEntityViezd.MestoSbora =
                dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdMestoSbora"].Value.ToString();
            _aEntityViezd.PoriadokSbora =
                (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_ViezdPoriadok"].Value;

            var form = new FormTourBron(this, _connectionManager, _dataManager, _aEntityClient, _aEntityNapravlenie,
                                        _aEntityGorod, _aEntityBazaOtdiha, _aEntityUslovia, _aEntityKomnata,
                                        _aEntityTransport, _aEntityBron, _aEntityViezd, _aEntityPrice);
            RunForm(form);
        }

        private void ButtonDeleteClick(object sender, EventArgs e)
        {
            if (dataGridViewBron.CurrentRow == null || dataGridViewBron.CurrentRow.Index < 0) return;
            var result = MessageBox.Show("Вы действительно хотите удалить этот заказ?", string.Empty,
                                         MessageBoxButtons.YesNo);
            if (result == DialogResult.No) return;
            _aEntityBron.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Номер"].Value;
            _aEntityPrice.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDPrice"].Value;
            _aEntityViezd.ID = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDViezd"].Value;
            _aEntityGroup.IDGroup = (int) dataGridViewBron.Rows[_currentRowIndex].Cells["Hide_IDGroup"].Value;

            int rowAffectedViezd = _dataManager.ManageDataInDb(_aEntityViezd.GetDeleteQuery(),
                                                               _connectionManager.GetConnection());
            int rowAffectedPrice = _dataManager.ManageDataInDb(_aEntityPrice.GetDeleteQuery(),
                                                               _connectionManager.GetConnection());
            int rowAffectedBron = _dataManager.ManageDataInDb(_aEntityBron.GetDeleteQuery(),
                                                              _connectionManager.GetConnection());
            _dataManager.ManageDataInDb(_aEntityGroup.GetDeleteQuery(), _connectionManager.GetConnection());

            if (rowAffectedBron.Equals(0) || rowAffectedPrice.Equals(0) || rowAffectedViezd.Equals(0))
            {
                MessageBox.Show("Ошибка удаления данных.");
            }

            _currentRowIndex = 0;
            UpdateDataTableBron();
        }

        private void DataGridViewBronCellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            ShowFormBron();
        }

        private void DataGridViewBronCellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
        }

        private void DataGridViewBronSelectionChanged(object sender, EventArgs e)
        {
            if (dataGridViewBron.CurrentRow == null || dataGridViewBron.CurrentRow.Index < 0) return;
            _currentRowIndex = dataGridViewBron.CurrentRow.Index;
            UpdateDetailsBlock();
        }

        #endregion

        #region +++ TAB BazaOtdiha

        private int _boNapravlenieId;
        private int _boGorodId;
        private DataTable _dataTableBazaOtdiha;

        private void ComboBoxNapravlenieSelectedValueChanged(object sender, EventArgs e)
        {
            if (comboBoxBazaOtdihaNapravlenie.Items.Count < 1 || comboBoxBazaOtdihaNapravlenie.SelectedValue == null)
                return;
            _boNapravlenieId = (int) comboBoxBazaOtdihaNapravlenie.SelectedValue;
            var dataTableFilteredByNapavlenie = _dataTableBazaOtdiha.AsEnumerable().
                Where(row => row.Field<int>("Hide_IDNapravlenie") == _boNapravlenieId).CopyToDataTable();

            FillComboBoxFromDataTable(comboBoxBazaOtdihaGorod, dataTableFilteredByNapavlenie, "Hide_IDGorod", "Город");
            _boGorodId = (int) comboBoxBazaOtdihaGorod.SelectedValue;
            var dataTableFilteredByGorod = _dataTableBazaOtdiha.AsEnumerable().
                Where(row => row.Field<int>("Hide_IDGorod") == _boGorodId).CopyToDataTable();
            CustomFillDataGridBazaOtdiha(dataTableFilteredByGorod);
        }

        private void ComboBoxGorodSelectedValueChanged(object sender, EventArgs e)
        {
            if (comboBoxBazaOtdihaGorod.Items.Count < 1 || comboBoxBazaOtdihaGorod.SelectedValue == null) return;
            _boGorodId = (int) comboBoxBazaOtdihaGorod.SelectedValue;
            var dataTableFilteredByGorod = _dataTableBazaOtdiha.AsEnumerable().
                Where(row => row.Field<int>("Hide_IDGorod") == _boGorodId).CopyToDataTable();
            CustomFillDataGridBazaOtdiha(dataTableFilteredByGorod);
        }

        private void CheckBoxShowAllCheckedChanged(object sender, EventArgs e)
        {
            FillDataGridBazaOtdiha();
        }

        private void FillComboBoxFromDataTable(ListControl comboBox, DataTable dataTable, string valueMember,
                                               string displayMember)
        {
            comboBoxBazaOtdihaNapravlenie.SelectedValueChanged -= ComboBoxNapravlenieSelectedValueChanged;
            comboBoxBazaOtdihaGorod.SelectedValueChanged -= ComboBoxGorodSelectedValueChanged;

            comboBox.DataSource = dataTable.AsEnumerable().
                Distinct(new DataTableComparer(displayMember)).
                CopyToDataTable().DefaultView;
            comboBox.ValueMember = valueMember;
            comboBox.DisplayMember = displayMember;

            comboBoxBazaOtdihaNapravlenie.SelectedValueChanged += ComboBoxNapravlenieSelectedValueChanged;
            comboBoxBazaOtdihaGorod.SelectedValueChanged += ComboBoxGorodSelectedValueChanged;
        }

        private void UpdateDataGridBazaOtdiha()
        {
            if (_connectionManager.CheckConnection())
            {
                _dataTableBazaOtdiha = _dataManager.GetDataTable(new EntityPol().GetSelectQuery(),
                                                                 _connectionManager.GetConnection());
                if (_dataTableBazaOtdiha != null)
                {
                    FillDataGridBazaOtdiha();
                    _currentRowIndex = 0;
                }
                else
                {
                    MessageBox.Show("Данные отсутствуют.");
                }
            }
            else
            {
                MessageBox.Show("Ошибка подключения к БД.");
            }
        }

        private void FillDataGridBazaOtdiha()
        {
            if (_dataTableBazaOtdiha.Rows.Count > 0)
            {
                checkBoxBazaOtdihaShowAll.Enabled = true;
                comboBoxBazaOtdihaNapravlenie.Enabled = true;
                comboBoxBazaOtdihaGorod.Enabled = true;

                if (checkBoxBazaOtdihaShowAll.Checked)
                {
                    comboBoxBazaOtdihaNapravlenie.Enabled = false;
                    comboBoxBazaOtdihaGorod.Enabled = false;
                    _boGorodId = 0;
                    _boNapravlenieId = 0;
                    CustomFillDataGridBazaOtdiha(_dataTableBazaOtdiha);
                }
                else
                {
                    comboBoxBazaOtdihaNapravlenie.Enabled = true;
                    comboBoxBazaOtdihaGorod.Enabled = true;

                    FillComboBoxFromDataTable(comboBoxBazaOtdihaNapravlenie, _dataTableBazaOtdiha, "Hide_IDNapravlenie",
                                              "Направление");
                    _boNapravlenieId = (int) comboBoxBazaOtdihaNapravlenie.SelectedValue;
                    var dataTableFilteredByNapavlenie = _dataTableBazaOtdiha.AsEnumerable().
                        Where(row => row.Field<int>("Hide_IDNapravlenie") == _boNapravlenieId).CopyToDataTable();

                    FillComboBoxFromDataTable(comboBoxBazaOtdihaGorod, dataTableFilteredByNapavlenie, "Hide_IDGorod",
                                              "Город");
                    _boGorodId = (int) comboBoxBazaOtdihaGorod.SelectedValue;
                    var dataTableFilteredByGorod = _dataTableBazaOtdiha.AsEnumerable().
                        Where(row => row.Field<int>("Hide_IDGorod") == _boGorodId).CopyToDataTable();
                    CustomFillDataGridBazaOtdiha(dataTableFilteredByGorod);
                }
            }
            else
            {
                checkBoxBazaOtdihaShowAll.Enabled = false;
                comboBoxBazaOtdihaNapravlenie.Enabled = false;
                comboBoxBazaOtdihaGorod.Enabled = false;
                comboBoxBazaOtdihaNapravlenie.DataSource = null;
                comboBoxBazaOtdihaGorod.DataSource = null;
                CustomFillDataGridBazaOtdiha(_dataTableBazaOtdiha);
                MessageBox.Show("Данные отсутствуют");
            }
        }

        private void CustomFillDataGridBazaOtdiha(DataTable dataTable)
        {
            if (dataTable != null)
            {
                dataGridViewBazaOtdiha.DataSource = dataTable;
                for (var i = 0; i < dataGridViewBazaOtdiha.Columns.Count; i++)
                {
                    if (dataGridViewBazaOtdiha.Columns[i].Name.StartsWith("Hide"))
                    {
                        dataGridViewBazaOtdiha.Columns[i].Visible = false;
                    }
                }
            }
            else
            {
                MessageBox.Show("Данные отсутствуют.");
            }
        }

        #endregion

        #region +++ TAB Clients

        private DataTable _dataTableClient;

        private void TextBoxSurnameTextChanged(object sender, EventArgs e)
        {
            textBoxClientTelephone.Text = string.Empty;
            var dataTableClientsFilteredBySurname = _dataTableClient.AsEnumerable().
                Where(
                    row => row.Field<string>("Фамилия").ToLower().StartsWith(textBoxClientSurname.Text.ToLower().Trim()));
            if (dataTableClientsFilteredBySurname.Any())
                CustomFillDataGridClient(dataTableClientsFilteredBySurname.CopyToDataTable());
            else
                CustomFillDataGridClient(_dataTableClient.Clone()); //заполняем таблицу пустыми строками
        }

        private void MaskedTextBoxTelephoneTextChanged(object sender, EventArgs e)
        {
            textBoxClientSurname.Text = string.Empty;
            var dataTableClientsFilteredByTelephone = _dataTableClient.AsEnumerable().
                Where(
                    row =>
                    row.Field<string>("Тел 2").ToSimpleTelephone().StartsWith(
                        textBoxClientTelephone.Text.ToLower().Trim()) ||
                    row.Field<string>("Тел 1").ToSimpleTelephone().StartsWith(
                        textBoxClientTelephone.Text.ToLower().Trim()));
            if (dataTableClientsFilteredByTelephone.Any())
                CustomFillDataGridClient(dataTableClientsFilteredByTelephone.CopyToDataTable());
            else
                CustomFillDataGridClient(_dataTableClient.Clone()); //заполняем таблицу пустыми строками
        }

        private void ButtonClientEditClick(object sender, EventArgs e)
        {
            var form = new FormClient(this, _dataManager, _connectionManager, new EntityClient());
            form.ShowDialog(this);
            UpdateDataGridClient();
        }

        private void UpdateDataGridClient()
        {
            if (_connectionManager.CheckConnection())
            {
                _dataTableClient = _dataManager.GetDataTable(new EntityClient().GetSelectQuery(),
                                                             _connectionManager.GetConnection());
                if (_dataTableClient != null)
                {
                    FillDataGridClient();
                }
                else
                {
                    MessageBox.Show("Данные отсутствуют.");
                }
            }
            else
            {
                MessageBox.Show("Ошибка подключения к БД.");
            }
        }

        private void FillDataGridClient()
        {
            if (_dataTableClient.Rows.Count > 0)
            {
                textBoxClientSurname.Enabled = true;
                textBoxClientTelephone.Enabled = true;
                buttonClientEdit.Enabled = true;
            }
            else
            {
                textBoxClientSurname.Enabled = false;
                textBoxClientTelephone.Enabled = false;
                buttonClientEdit.Enabled = false;
                MessageBox.Show("Данные отсутствуют");
            }
            CustomFillDataGridClient(_dataTableClient);
        }

        private void CustomFillDataGridClient(DataTable dataTable)
        {
            if (dataTable != null)
            {
                dataGridViewClient.DataSource = dataTable;
                for (var i = 0; i < dataGridViewClient.Columns.Count; i++)
                {
                    if (dataGridViewClient.Columns[i].Name.StartsWith("Hide"))
                    {
                        dataGridViewClient.Columns[i].Visible = false;
                    }
                }

                for (var row = 0; row < dataGridViewClient.Rows.Count; row++)
                {
                    if ((bool) dataGridViewClient.Rows[row].Cells["Hide_BlackList"].Value)
                    {
                        dataGridViewClient.Rows[row].DefaultCellStyle.BackColor = Color.Pink;
                        dataGridViewClient.Rows[row].DefaultCellStyle.SelectionBackColor = Color.PaleVioletRed;
                    }
                }
            }
            else
            {
                MessageBox.Show("Данные отсутствуют.");
            }
        }

        #endregion

        #region +++ TAB Transport

        private DataTable _dataTableTransport;

        private void UpdateDataGridTransport()
        {
            if (_connectionManager.CheckConnection())
            {
                //var str = _aEntityPot.GetSelectQuery();
                _dataTableTransport = _dataManager.GetDataTable(new EntityTransport().GetSelectQuery(),
                                                                _connectionManager.GetConnection());
                if (_dataTableTransport != null)
                {
                    FillDataGridTransport();
                }
                else
                {
                    MessageBox.Show("Данные отсутствуют");
                }
            }
            else
            {
                MessageBox.Show("Ошибка подключения к БД.");
            }
        }

        private void FillDataGridTransport()
        {
            CustomFillDataGridTransport(_dataTableTransport);
        }

        private void CustomFillDataGridTransport(DataTable dataTable)
        {
            if (dataTable != null)
            {
                dataGridViewTransport.DataSource = dataTable;
                //скрываем все ненужные столбцы
                for (var i = 0; i < dataGridViewTransport.Columns.Count; i++)
                {
                    if (dataGridViewTransport.Columns[i].Name.StartsWith("Hide"))
                    {
                        dataGridViewTransport.Columns[i].Visible = false;
                    }
                }
            }
            else
            {
                MessageBox.Show("Данные отсутствуют.");
            }
        }

        #endregion

        private void TabControlSelectedIndexChanged(object sender, EventArgs e)
        {
            switch (tabControl.SelectedIndex)
            {
                case 0:
                    UpdateDataTableBron();
                    break;
                case 1:
                    UpdateDataGridBazaOtdiha();
                    break;
                case 2:
                    UpdateDataGridClient();
                    break;
                case 3://Баланс туда
                    AsyncWorker.RunAcync(this,
                                         delegate
                                             {
                                                 сводка_на_рейсTableAdapter.Fill(dbDataSet.Сводка_на_рейс);
                                             },
                                         delegate
                                             {
                                                 reportViewerSummaryTour.LocalReport.ReportEmbeddedResource =
                                                     "TAProject.Reports.ReportSummaryTour.rdlc";
                                                 reportViewerSummaryTour.LocalReport.DataSources.Add(new ReportDataSource
                                                                                                         {
                                                                                                             Name ="DataSetMain",
                                                                                                             Value =dbDataSet.Сводка_на_рейс
                                                                                                         });
                                                 reportViewerSummaryTour.RefreshReport();
                                             });
                    break;
                case 4://Баланс назад
                    AsyncWorker.RunAcync(this,
                                         delegate { сводка_на_рейс_назадTableAdapter.Fill(dbDataSet.Сводка_на_рейс_назад); },
                                         delegate { reportViewerSummaryTourCome.RefreshReport(); });
                    break;
                case 5://Сводка туда
                    #region Сводка туда
                    AsyncWorker.RunAcync(this,
                                         delegate { заказыTableAdapter.Fill(dbDataSet.Заказы); },
                                         delegate
                                             {
                                                 comboBoxDateStartFilter.SelectedValueChanged -= ComboBoxDateStartFilterSelectedValueChanged;
                                                 comboBoxStartNapravlenie.SelectedValueChanged -= ComboBoxStartNapravlenieSelectedValueChanged;

                                                 reportViewerByDateOfSend.LocalReport.DataSources.Clear();

                                                 var listForComboBoxNapravlenie = dbDataSet.Заказы.Select(napr =>
                                                     new ComboBoxFiller<string>(napr.Направление, napr.Направление)).
                                                     ToList().
                                                     Distinct(new ComboBoxItemsComparer<string>()).
                                                     OrderBy(napr => napr.Value);

                                                 comboBoxStartNapravlenie.Items.Clear();
                                                 comboBoxStartNapravlenie.Items.AddRange(listForComboBoxNapravlenie.ToArray());
                                                 comboBoxStartNapravlenie.SelectedIndex = 0;

                                                 var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxStartNapravlenie.SelectedItem).Value;

                                                 var listForComboBoxDates = dbDataSet.Заказы.AsEnumerable().
                                                     Where(row => row.Направление.Equals(selectedNapravlenie)).
                                                     Select(dates =>new ComboBoxFiller<DateTime>(dates.Начало, DateTime.Parse(dates.Начало))).                                                         
                                                         ToList().
                                                         Distinct(new ComboBoxItemsComparer<DateTime>()).
                                                         OrderBy(row => row.Value);

                                                 comboBoxDateStartFilter.Items.Clear();
                                                 comboBoxDateStartFilter.Items.AddRange(listForComboBoxDates.ToArray());
                                                 comboBoxDateStartFilter.SelectedIndex = 0;

                                                 var reportDataSource = new ReportDataSource
                                                 {
                                                     Name = "DataSetMain",
                                                     Value = dbDataSet.Заказы.
                                                         Where(row => row.Начало.CompareDates(((ComboBoxFiller<DateTime>)comboBoxDateStartFilter.SelectedItem).Value.ToShortDateString()) && 
                                                             row.Направление.Equals(((ComboBoxFiller<string>) comboBoxStartNapravlenie.SelectedItem).Value)).
                                                         CopyToDataTable()
                                                 };

                                                 reportViewerByDateOfSend.LocalReport.DataSources.Add(reportDataSource);

                                                 reportViewerByDateOfSend.LocalReport.ReportEmbeddedResource =
                                                     "TAProject.Reports.ReportByDateOfSend.rdlc";
                                                 reportViewerByDateOfSend.RefreshReport();

                                                 comboBoxStartNapravlenie.SelectedValueChanged += ComboBoxStartNapravlenieSelectedValueChanged;
                                                 comboBoxDateStartFilter.SelectedValueChanged += ComboBoxDateStartFilterSelectedValueChanged;
                                             });
                    break;
                    #endregion
                case 6://Сводка назад
                    #region Сводка назад
                    AsyncWorker.RunAcync(this,
                                         delegate { заказыTableAdapter.Fill(dbDataSet.Заказы); },
                                         delegate
                                         {
                                             comboBoxDateEndFilter.SelectedValueChanged -= ComboBoxDateEndFilterSelectedValueChanged;
                                             comboBoxEndNapravlenie.SelectedValueChanged -= ComboBoxEndNapravlenieSelectedValueChanged;

                                             reportViewerByDateOfCome.LocalReport.DataSources.Clear();

                                             var listForComboBoxNapravlenie = dbDataSet.Заказы.Select(napr =>
                                                 new ComboBoxFiller<string>(napr.Направление, napr.Направление)).
                                                 ToList().
                                                 Distinct(new ComboBoxItemsComparer<string>()).
                                                 OrderBy(napr => napr.Value);

                                             comboBoxEndNapravlenie.Items.Clear();
                                             comboBoxEndNapravlenie.Items.AddRange(listForComboBoxNapravlenie.ToArray());
                                             comboBoxEndNapravlenie.SelectedIndex = 0;

                                             var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxEndNapravlenie.SelectedItem).Value;

                                             var listForComboBoxDates = dbDataSet.Заказы.AsEnumerable().
                                                 Where(row => row.Направление.Equals(selectedNapravlenie)).
                                                 Select(dates => new ComboBoxFiller<DateTime>(dates.Конец, DateTime.Parse(dates.Конец))).
                                                     ToList().
                                                     Distinct(new ComboBoxItemsComparer<DateTime>()).
                                                     OrderBy(row => row.Value);

                                             comboBoxDateEndFilter.Items.Clear();
                                             comboBoxDateEndFilter.Items.AddRange(listForComboBoxDates.ToArray());
                                             comboBoxDateEndFilter.SelectedIndex = 0;

                                             var reportDataSource = new ReportDataSource
                                             {
                                                 Name = "DataSetMain",
                                                 Value = dbDataSet.Заказы.
                                                     Where(row => row.Конец.CompareDates(((ComboBoxFiller<DateTime>)comboBoxDateEndFilter.SelectedItem).Value.ToShortDateString()) &&
                                                         row.Направление.Equals(((ComboBoxFiller<string>)comboBoxEndNapravlenie.SelectedItem).Value)).
                                                     CopyToDataTable()
                                             };

                                             reportViewerByDateOfCome.LocalReport.DataSources.Add(reportDataSource);

                                             reportViewerByDateOfCome.LocalReport.ReportEmbeddedResource =
                                                 "TAProject.Reports.ReportByDateOfCome.rdlc";
                                             reportViewerByDateOfCome.RefreshReport();

                                             comboBoxEndNapravlenie.SelectedValueChanged += ComboBoxEndNapravlenieSelectedValueChanged;
                                             comboBoxDateEndFilter.SelectedValueChanged += ComboBoxDateEndFilterSelectedValueChanged;
                                         });
                    break;
                    #endregion
                case 7:
                    UpdateDataGridTransport();
                    break;
            }
        }

        private void MainForm_Load(object sender, EventArgs e)
        {

        }

        #region Сводка туда
        private void ComboBoxStartNapravlenieSelectedValueChanged(object sender, EventArgs e)
        {
            var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxStartNapravlenie.SelectedItem).Value;

            comboBoxDateStartFilter.SelectedValueChanged -= ComboBoxDateStartFilterSelectedValueChanged;

            var listForComboBoxDates = dbDataSet.Заказы.AsEnumerable().
                Where(row => row.Направление.Equals(selectedNapravlenie)).
                Select(dates => new ComboBoxFiller<DateTime>(dates.Начало, DateTime.Parse(dates.Начало))).
                    ToList().
                    Distinct(new ComboBoxItemsComparer<DateTime>()).
                    OrderBy(row => row.Value);

            comboBoxDateStartFilter.Items.Clear();
            comboBoxDateStartFilter.Items.AddRange(listForComboBoxDates.ToArray());
            comboBoxDateStartFilter.SelectedIndex = 0;

            ComboBoxDateStartFilterSelectedValueChanged(null, null);

            comboBoxDateStartFilter.SelectedValueChanged += ComboBoxDateStartFilterSelectedValueChanged;
        }

        private void ComboBoxDateStartFilterSelectedValueChanged(object sender, EventArgs e)
        {
            var reportDataSource =
                reportViewerByDateOfSend.LocalReport.DataSources["DataSetMain"];
            reportDataSource.Value = dbDataSet.Заказы.
                    Where(row => row.Начало.CompareDates(((ComboBoxFiller<DateTime>)comboBoxDateStartFilter.SelectedItem).Value.ToShortDateString()) &&
                        row.Направление.Equals(((ComboBoxFiller<string>)comboBoxStartNapravlenie.SelectedItem).Value)).
                    CopyToDataTable();

            reportViewerByDateOfSend.RefreshReport();
        }

        private void ButtonReportWaybillClick(object sender, EventArgs e)
        {
            var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxStartNapravlenie.SelectedItem).Value;
            var selectedDate =
                ((ComboBoxFiller<DateTime>) comboBoxDateStartFilter.SelectedItem).Value.ToShortDateString();

            AsyncWorker.RunAcync(this, 
                delegate
                    {
                        var отчетДорогаTableAdapter = new dbDataSetTableAdapters.Отчет_дорогаTableAdapter 
                        {ClearBeforeFill = true};
                        отчетДорогаTableAdapter.Fill(dbDataSet.Отчет_дорога);
                    }, 
                delegate
                    {
                        var reportDataSourceMain = new ReportDataSource
                                                       {
                                                           Name = "DataSetMain",
                                                           Value = dbDataSet.Отчет_дорога.Where(row => row.Начало.CompareDates(selectedDate) && 
                                                               row.Направление_Название.Equals(selectedNapravlenie))
                                                       };

                        var reportDataSourceCollection =  new Collection<ReportDataSource>
                                                              {
                                                                  reportDataSourceMain
                                                              };
                        var reportParameterCollection = new ReportParameterCollection
                                                            {
                                                                new ReportParameter("ReportParameterNapravlenie", selectedNapravlenie),
                                                                new ReportParameter("ReportParameterDate", selectedDate)
                                                            };

                        var form = new FormReportPlaceHolder(this, reportDataSourceCollection, reportParameterCollection,
                            "TAProject.Reports.ReportRoad.rdlc");
                        form.ShowDialog(this);
                    });
        }

        private void ButtonReportSettleClick(object sender, EventArgs e)
        {
            var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxStartNapravlenie.SelectedItem).Value;
            var selectedDate =
                ((ComboBoxFiller<DateTime>)comboBoxDateStartFilter.SelectedItem).Value.ToShortDateString();

            AsyncWorker.RunAcync(this,
                delegate
                {
                    var reportSettleTableAdapter = new dbDataSetTableAdapters.Отчет_поселениеTableAdapter{ClearBeforeFill = true};
                    reportSettleTableAdapter.Fill(dbDataSet.Отчет_поселение);
                },
                delegate
                {
                    var activeOrderIds = dbDataSet.Заказы.Where(
                            row =>row.Начало.CompareDates(((ComboBoxFiller<DateTime>) comboBoxDateStartFilter.SelectedItem).Value.ToShortDateString()) &&
                            row.Направление.Equals(((ComboBoxFiller<string>) comboBoxStartNapravlenie.SelectedItem).Value)).
                        Select(row => row.Идентификатор).ToList();

                    var reportDataSourceMain = new ReportDataSource{
                                                       Name = "DataSetMain",
                                                       Value = dbDataSet.Отчет_поселение.Where(row => activeOrderIds.Contains(row.Идентификатор))
                    };

                    var reportDataSourceCollection = new Collection<ReportDataSource>
                                                              {
                                                                  reportDataSourceMain
                                                              };
                    var reportParameterCollection = new ReportParameterCollection
                                                            {
                                                                new ReportParameter("ReportParameterNapravlenie", selectedNapravlenie),
                                                                new ReportParameter("ReportParameterDate", selectedDate)
                                                            };

                    var form = new FormReportPlaceHolder(this, reportDataSourceCollection, reportParameterCollection,
                        "TAProject.Reports.ReportSettle.rdlc");
                    form.ShowDialog(this);
                });
        }

        private void ButtonReportPassengerListClick(object sender, EventArgs e)
        {
            var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxStartNapravlenie.SelectedItem).Value;
            var selectedDate =
                ((ComboBoxFiller<DateTime>)comboBoxDateStartFilter.SelectedItem).Value.ToShortDateString();

            AsyncWorker.RunAcync(this,
                delegate
                {
                    #region Заполнение таблицы Пассажиры
                    dbDataSet.Пассажиры.Clear();

                    var reportPassengersTableAdapter = new dbDataSetTableAdapters.СписокПассажировTableAdapter { ClearBeforeFill = true };
                    reportPassengersTableAdapter.Fill(dbDataSet.СписокПассажиров);

                    var passengers = new List<Passenger>();
                    passengers.AddRange(dbDataSet.СписокПассажиров
                        .Where(row => row.Начало.CompareDates(selectedDate) && row.Направление.Equals(selectedNapravlenie))
                        .Select(row => new Passenger(row.ФамилияКлиент, row.ИмяКлиент,row.ОтчествоКлиент, row.Направление, row.Начало, row.Конец))
                        .ToList()
                        .Distinct(new AllTypesItemsComparer<Passenger>()));
//                    MessageBox.Show(passengers.Count.ToString());
                    foreach (var passenger in passengers)
                    {
                        dbDataSet.Пассажиры.AddПассажирыRow(passenger.Surname, passenger.Name, passenger.FatherName,
                                                            passenger.Napravlenie, passenger.Start, passenger.End);

                        var filteredBySurname = dbDataSet.СписокПассажиров
                            .Where(row => row.Начало.CompareDates(selectedDate) && row.Направление.Equals(selectedNapravlenie))
                            .Where(row => row.ФамилияКлиент == passenger.Surname)
                            .Where(row => !row.IsФамилияГруппаNull() && !row.IsИмяГруппаNull() && !row.IsОтчествоГруппаNull())
                            .Select(row => new Passenger(row.ФамилияГруппа, row.ИмяГруппа, row.ОтчествоГруппа, row.Направление, row.Начало, row.Конец))
                            .ToList();

//                        var groupList = filteredBySurname.Aggregate(string.Empty, (current, item) => current + item.Surname + "\n");
//                        MessageBox.Show(String.Format("Пассажир: {0}; \n Гуппа:\n{1}", passenger.Surname, groupList));

                        foreach (var subPassenger in filteredBySurname)
                        {
                            dbDataSet.Пассажиры.AddПассажирыRow(subPassenger.Surname, subPassenger.Name, subPassenger.FatherName,
                                                                subPassenger.Napravlenie, subPassenger.Start, subPassenger.End);
                        }
                    }
                    #endregion
                },
                delegate
                {
                    var reportDataSourceMain = new ReportDataSource
                    {
                        Name = "DataSetMain",
                        Value = dbDataSet.Пассажиры.Where(row => row.Начало.CompareDates(selectedDate) &&
                                                      row.Направление.Equals(selectedNapravlenie))
                    };

                    var reportDataSourceCollection = new Collection<ReportDataSource>
                                        {
                                            reportDataSourceMain
                                        };
                    var reportParameterCollection = new ReportParameterCollection
                                        {
                                            new ReportParameter("ReportParameterNapravlenie", selectedNapravlenie),
                                            new ReportParameter("ReportParameterDate", selectedDate)
                                        };

                    var form = new FormReportPlaceHolder(this, reportDataSourceCollection, reportParameterCollection,
                        "TAProject.Reports.ReportPassengers.rdlc");
                    form.ShowDialog(this);
                });
        }
        #endregion

        #region Сводка назад
        private void ComboBoxDateEndFilterSelectedValueChanged(object sender, EventArgs e)
        {
            var reportDataSource =
                reportViewerByDateOfCome.LocalReport.DataSources["DataSetMain"];
            reportDataSource.Value = dbDataSet.Заказы.
                Where(row => row.Конец.CompareDates(((ComboBoxFiller<DateTime>)comboBoxDateEndFilter.SelectedItem).Value.ToShortDateString()) &&
                        row.Направление.Equals(((ComboBoxFiller<string>)comboBoxEndNapravlenie.SelectedItem).Value)).
                CopyToDataTable();

            reportViewerByDateOfCome.RefreshReport();
        }

        private void ComboBoxEndNapravlenieSelectedValueChanged(object sender, EventArgs e)
        {
            var selectedNapravlenie = ((ComboBoxFiller<string>)comboBoxEndNapravlenie.SelectedItem).Value;

            comboBoxDateEndFilter.SelectedValueChanged -= ComboBoxDateEndFilterSelectedValueChanged;

            var listForComboBoxDates = dbDataSet.Заказы.AsEnumerable().
                Where(row => row.Направление.Equals(selectedNapravlenie)).
                Select(dates => new ComboBoxFiller<DateTime>(dates.Конец, DateTime.Parse(dates.Конец))).
                    ToList().
                    Distinct(new ComboBoxItemsComparer<DateTime>()).
                    OrderBy(row => row.Value);

            comboBoxDateEndFilter.Items.Clear();
            comboBoxDateEndFilter.Items.AddRange(listForComboBoxDates.ToArray());
            comboBoxDateEndFilter.SelectedIndex = 0;

            ComboBoxDateEndFilterSelectedValueChanged(null, null);

            comboBoxDateEndFilter.SelectedValueChanged += ComboBoxDateEndFilterSelectedValueChanged;
        }
        #endregion
    }

    public class Passenger
    {
        public string Surname { set; get; }
        public string Name { set; get; }
        public string FatherName { set; get; }
        public string Napravlenie { set; get; }
        public string Start { set; get; }
        public string End { set; get; }

        public Passenger(string surname, string name, string fatherName, string napravlenie, string start, string end)
        {
            Surname = surname;
            Name = name;
            FatherName = fatherName;
            Napravlenie = napravlenie;
            Start = start;
            End = end;
        }

        public override string ToString()
        {
            return Surname;
        }
    }
}